﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TTTTBHYT.VO;

namespace TTTTBHYT.DAO
{
    public class Mau29DAO
    {
        private dbConnection conn;
        public Mau29DAO()
        {
            conn = new dbConnection();
        }
        public DataTable SearchBySoKCB(string soKCB)
        {
            string query = string.Format("select * from MAU29 "
            + " where SoKCB = @soKCB");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@soKCB", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(soKCB);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchBySoKCBFromBHXH(string soKCB)
        {
            string query = string.Format("select * from hosocn "
            + " where SoKCB = @soKCB");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@soKCB", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(soKCB);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchAll()
        {
            string query = string.Format("select * from MAU29 order by hoten");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable Search(string condition)
        {
            string query = string.Format("select * from MAU29 where 1 = 1 " + condition + " order by hoten");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public MAU29VO InsertRecord(MAU29VO Mau29VO)
        {
            string keySL;
            string query = string.Format("INSERT INTO [MISBHYT_NT].[dbo].[MAU29]([SOKCB], [HOTEN], [NGAYSINH], "
            + " [GIOITINH], [DIACHI], [MADT], [MA_BV], [MA_TINH], [TUNGAY], [DENNGAY], [MA_BV_DT], [MA_TINH_DT]," 
            + " [TUNGAYDT], [DENNGAYDT], [SONGAY], [LOAIDT], [HANGBV], [TTNV], [LOAIGT], [THEBHYT], [TUYENKHAM],"
            + " [KETLUAN], [THANHTOAN], [QUY], [XETNGHIEM], [HA], [THUOC], [MAU], [THUTHUAT], [VATTU], [TIENKHAM],"
            + " [PHAUTHUATCAO], [VATTUCAO], [LAMSANGCAO], [THUOCK], [XETNGHIEMKCB], [HAKCB], [THUOCKCB], [MAUKCB],"
            + " [THUTHUATKCB], [VATTUKCB], [TIENKHAMKCB], [PHAUTHUATCAOKCB], [VATTUCAOKCB], [LAMSANGCAOKCB], [THUOCKKCB],"
            + " [XETNGHIEMLYDO], [HALYDO], [THUOCLYDO], [MAULYDO], [THUTHUATLYDO], [VATTULYDO], [TIENKHAMLYDO],"
            + " [PHAUTHUATCAOLYDO], [VATTUCAOLYDO], [LAMSANGCAOLYDO], [THUOCKLYDO], [TIENTHANHTOAN], [NBTHANHTOAN],[BHXHTHANHTOAN])"
            + " VALUES(@sokcb, @hoten, @ngaysinh, @gioitinh, @diachi,@madt, @mabv, @matinh, @tungay, @denngay, @mabvdt,"
            + " @matinhdt, @tungaydt,@denngaydt, @songay, @loaidt, @hangbv, @ttnv, @loaigt, @thebhyt, @tuyenkham, @ketluan, @thanhtoan,"
            + " @quy, @xetnghiem, @ha, @thuoc, @mau, @thuthuat, @vattu, @tienkham, @phauthuatcao, @vattucao, @lamsangcao, @thuock,"
            + " @xetnghiemkcb, @hakcb, @thuockcb, @maukcb, @thuthuatkcb, @vattukcb, @tienkhamkcb, @phauthuatcaokcb, @vattucaokcb, @lamsangcaokcb, @thuockkcb,"
            + " @xetnghiemlydo, @halydo, @thuoclydo, @maulydo, @thuthuatlydo, @vattulydo, @tienkhamlydo, @phauthuatcaolydo, @vattucaolydo, @lamsangcaolydo, @thuocklydo,"
            + " @tienthanhtoan, @nbthanhtoan, @bhxhthanhtoan)");


            SqlParameter[] sqlParameters = new SqlParameter[52];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(Module.CreateRunninNo(rnd));

            keySL = sqlParameters[0].Value.ToString();

            sqlParameters[1] = new SqlParameter("@tenGiaiDoan", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(GiaiDoanVO.TenGiaiDoan);

            sqlParameters[2] = new SqlParameter("@keyslcongtrinh", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(GiaiDoanVO.CongTrinh.KeySL);

            sqlParameters[3] = new SqlParameter("@phutrach", SqlDbType.NVarChar);
            sqlParameters[3].Value = Convert.ToString(GiaiDoanVO.PhuTrach);
            //dat          
            sqlParameters[4] = new SqlParameter("@dat_mt_nguyendang", SqlDbType.Int);
            sqlParameters[4].Value = Convert.ToString(GiaiDoanVO.Dat_MT_Nguyendang);

            sqlParameters[5] = new SqlParameter("@dat_mt_roi", SqlDbType.Int);
            sqlParameters[5].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Roi);

            sqlParameters[6] = new SqlParameter("@dat_mt_vatlieu", SqlDbType.Int);
            sqlParameters[6].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Vatlieu);

            sqlParameters[7] = new SqlParameter("@dat_mt_doam", SqlDbType.Int);
            sqlParameters[7].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Doam);

            sqlParameters[8] = new SqlParameter("@dat_mb_nguyendang", SqlDbType.Int);
            sqlParameters[8].Value = Convert.ToString(GiaiDoanVO.Dat_MB_Nguyendang);

            sqlParameters[9] = new SqlParameter("@dat_mb_roi", SqlDbType.Int);
            sqlParameters[9].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Roi);

            sqlParameters[10] = new SqlParameter("@dat_mb_vatlieu", SqlDbType.Int);
            sqlParameters[10].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Vatlieu);

            sqlParameters[11] = new SqlParameter("@dat_mb_doam", SqlDbType.Int);
            sqlParameters[11].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Doam);

            sqlParameters[12] = new SqlParameter("@dat_mkxb_nguyendang", SqlDbType.Int);
            sqlParameters[12].Value = Convert.ToString(GiaiDoanVO.Dat_MKXB_Nguyendang);

            sqlParameters[13] = new SqlParameter("@dat_mkxb_roi", SqlDbType.Int);
            sqlParameters[13].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Roi);

            sqlParameters[14] = new SqlParameter("@dat_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[14].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Vatlieu);

            sqlParameters[15] = new SqlParameter("@dat_mkxb_doam", SqlDbType.Int);
            sqlParameters[15].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Doam);
            //dat
            sqlParameters[16] = new SqlParameter("@da_mt_coly", SqlDbType.Int);
            sqlParameters[16].Value = Convert.ToInt32(GiaiDoanVO.Da_MT_Coly);

            sqlParameters[17] = new SqlParameter("@da_mt_vatlieu", SqlDbType.Int);
            sqlParameters[17].Value = Convert.ToInt32(GiaiDoanVO.Da_MT_Vatlieu);

            sqlParameters[18] = new SqlParameter("@da_mb_coly", SqlDbType.Int);
            sqlParameters[18].Value = Convert.ToInt32(GiaiDoanVO.Da_MB_Coly);

            sqlParameters[19] = new SqlParameter("@da_mb_vatlieu", SqlDbType.Int);
            sqlParameters[19].Value = Convert.ToInt32(GiaiDoanVO.Da_MB_Vatlieu);

            sqlParameters[20] = new SqlParameter("@da_mkxb_coly", SqlDbType.Int);
            sqlParameters[20].Value = Convert.ToInt32(GiaiDoanVO.Da_MKXB_Coly);

            sqlParameters[21] = new SqlParameter("@da_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[21].Value = Convert.ToInt32(GiaiDoanVO.Da_MKXB_Vatlieu);
            //cat            
            sqlParameters[22] = new SqlParameter("@cat_mt_catnen", SqlDbType.Int);
            sqlParameters[22].Value = Convert.ToInt32(GiaiDoanVO.Cat_MT_Catnen);

            sqlParameters[23] = new SqlParameter("@cat_mt_vatlieu", SqlDbType.Int);
            sqlParameters[23].Value = Convert.ToInt32(GiaiDoanVO.Cat_MT_Vatlieu);

            sqlParameters[24] = new SqlParameter("@cat_mb_catnen", SqlDbType.Int);
            sqlParameters[24].Value = Convert.ToInt32(GiaiDoanVO.Cat_MB_Catnen);

            sqlParameters[25] = new SqlParameter("@cat_mb_vatlieu", SqlDbType.Int);
            sqlParameters[25].Value = Convert.ToInt32(GiaiDoanVO.Cat_MB_Vatlieu);

            sqlParameters[26] = new SqlParameter("@cat_mkxb_catnen", SqlDbType.Int);
            sqlParameters[26].Value = Convert.ToInt32(GiaiDoanVO.Cat_MKXB_Catnen);

            sqlParameters[27] = new SqlParameter("@cat_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[27].Value = Convert.ToInt32(GiaiDoanVO.Cat_MKXB_Vatlieu);
            //nuoc
            sqlParameters[28] = new SqlParameter("@nuoc_mt_betong", SqlDbType.Int);
            sqlParameters[28].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MT_Betong);

            sqlParameters[29] = new SqlParameter("@nuoc_mt_visinh", SqlDbType.Int);
            sqlParameters[29].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MT_Visinh);

            sqlParameters[30] = new SqlParameter("@nuoc_mb_betong", SqlDbType.Int);
            sqlParameters[30].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MB_Betong);

            sqlParameters[31] = new SqlParameter("@nuoc_mb_visinh", SqlDbType.Int);
            sqlParameters[31].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MB_Visinh);

            sqlParameters[32] = new SqlParameter("@nuoc_mkxb_betong", SqlDbType.Int);
            sqlParameters[32].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MKXB_Betong);

            sqlParameters[33] = new SqlParameter("@nuoc_mkxb_visinh", SqlDbType.Int);
            sqlParameters[33].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MKXB_Visinh);
            //thach hoc
            sqlParameters[34] = new SqlParameter("@thachhoc_mt", SqlDbType.Int);
            sqlParameters[34].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MT);

            sqlParameters[35] = new SqlParameter("@thachhoc_mb", SqlDbType.Int);
            sqlParameters[35].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MB);

            sqlParameters[36] = new SqlParameter("@thachhoc_mkxb", SqlDbType.Int);
            sqlParameters[36].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MKXB);
            //thuy van
            sqlParameters[37] = new SqlParameter("@thuyvan_mt", SqlDbType.Int);
            sqlParameters[37].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MT);

            sqlParameters[38] = new SqlParameter("@thuyvan_mb", SqlDbType.Int);
            sqlParameters[38].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MB);

            sqlParameters[39] = new SqlParameter("@thuyvan_mkxb", SqlDbType.Int);
            sqlParameters[39].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MKXB);
            //cong
            sqlParameters[40] = new SqlParameter("@cong_mt_ngoaigio", SqlDbType.Float);
            sqlParameters[40].Value = Convert.ToInt32(GiaiDoanVO.Cong_MT_Ngoaigio);

            sqlParameters[41] = new SqlParameter("@cong_mt_tronggio", SqlDbType.Float);
            sqlParameters[41].Value = Convert.ToInt32(GiaiDoanVO.Cong_MT_Tronggio);

            sqlParameters[42] = new SqlParameter("@cong_mb_mauboc", SqlDbType.Float);
            sqlParameters[42].Value = Convert.ToInt32(GiaiDoanVO.Cong_MB_Boc);

            sqlParameters[43] = new SqlParameter("@cong_mb_ISO", SqlDbType.Float);
            sqlParameters[43].Value = Convert.ToInt32(GiaiDoanVO.Cong_MB_ISO);

            sqlParameters[44] = new SqlParameter("@cong_nhanuoc", SqlDbType.Float);
            sqlParameters[44].Value = Convert.ToInt32(GiaiDoanVO.Cong_Nhanuoc);

            sqlParameters[45] = new SqlParameter("@slthinghiem", SqlDbType.Int);
            sqlParameters[45].Value = Convert.ToInt32(GiaiDoanVO.SlThiNghiem);

            sqlParameters[46] = new SqlParameter("@slthamchieu", SqlDbType.Int);
            sqlParameters[46].Value = Convert.ToInt32(GiaiDoanVO.SlThamChieu);

            sqlParameters[47] = new SqlParameter("@thoigian", SqlDbType.NVarChar);
            sqlParameters[47].Value = Convert.ToString(GiaiDoanVO.ThoiGian);

            sqlParameters[48] = new SqlParameter("@nsthinghiem", SqlDbType.Float);
            sqlParameters[48].Value = Convert.ToString(GiaiDoanVO.NSThiNghiem);

            sqlParameters[49] = new SqlParameter("@nsthamchieu", SqlDbType.Float);
            sqlParameters[49].Value = Convert.ToString(GiaiDoanVO.NSThamChieu);

            sqlParameters[50] = new SqlParameter("@nsbq", SqlDbType.Float);
            sqlParameters[50].Value = Convert.ToString(GiaiDoanVO.NSBQ);

            sqlParameters[51] = new SqlParameter("@thoigianden", SqlDbType.NVarChar);
            sqlParameters[51].Value = Convert.ToString(GiaiDoanVO.ThoiGianDen);
            if (conn.executeInsertQuery(query, sqlParameters))
            {
               return GiaiDoanVO;
            }
            else
            {
                return null;
            }
        }
    }
}
